package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import bean.Flight;
import bean.Journey;
import bean.ReservationDetails;
import exception.DAOException;

public class JourneyDAOImpl extends DAO implements JourneyDAO {

	@Override
	public List<Journey> reserve(int numTickets, Journey[] journey, long  personId) throws DAOException{
		Statement stmt = null;
		Connection connection = null;
		List<Journey> lstJourney = new ArrayList<Journey>();
		try {
			connection = getConnection();
			if (connection != null) {
				stmt = connection.createStatement();
				for(Journey j : journey){
					stmt.addBatch("insert into ars1.journey " +
							"(person_id, flight_id, seats_booked, boarding_point, journey_date, " +
							"ticket_issue_date, confirmation_number)  select "+personId+","+j.getFlight().getFlightId()
							+","+numTickets+",'"+j.getBoardingPoint().toUpperCase()+"','"+j.getDateOfJourney()+"', now(),  max(confirmation_number)+1 from ars1.journey;");
					
					System.out.println("insert into ars1.journey " +
							"(person_id, flight_id, seats_booked, boarding_point, journey_date, " +
							"ticket_issue_date, confirmation_number)  select "+personId+","+j.getFlight().getFlightId()
							+","+numTickets+",'"+j.getBoardingPoint().toUpperCase()+"','"+j.getDateOfJourney()+"', now(),  max(confirmation_number)+1 from ars1.journey;");
					
					for(ReservationDetails r : j.getReservationDetails()){
						stmt.addBatch("insert into ars1.reservation_details" +
								" (confirmation_number, first_name, last_name, age) select max(confirmation_number), '"+r.getFirstName().toUpperCase()+"', " +
										"'"+r.getLastName().toUpperCase()+"', "+r.getAge()+" from ars1.journey;");
						
					}
					
					
				}
				
				//TBD - one more batch stmt to be added to update the transaction table
				
				if(stmt.executeBatch().length > 0){
					String sqlStmt = null;
					if(journey.length == 1){
						sqlStmt = "select max(confirmation_number) num from ars1.journey;";
						stmt = connection.createStatement();
						ResultSet resultSet = stmt.executeQuery(sqlStmt);
						while(resultSet.next()){
							journey[0].setConfirmationNumber(resultSet.getLong("num"));
						}
						
						lstJourney.add(journey[0]);
						
					}else{
						sqlStmt = "select * from ars1.journey where confirmation_number in " +
								"((select  max(confirmation_number)-1 from ars1.journey), " +
								"(select max(confirmation_number)  from ars1.journey));";
						stmt = connection.createStatement();
						ResultSet resultSet = stmt.executeQuery(sqlStmt);
						while(resultSet.next()){
							for (int i=0; i<2; i++){
								if(journey[i].getFlight().getFlightId() == resultSet.getLong("flight_id")){
									journey[i].setConfirmationNumber(resultSet.getLong("confirmation_number"));
									lstJourney.add(journey[i]);
								}
							}
						}
					}
				}else{
					System.out.println("Error while making a reservation");
				}			

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DAOException(e.getMessage());
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {
					new DAOException(ex.getMessage());
			}

		}
		return lstJourney;

	}
	
	@Override
	public Journey issueTicket(long confirmationNumber) throws DAOException{
		PreparedStatement stmt = null;
		Connection connection = null;
		Journey journey = new Journey();
		Flight flight = new Flight();
		List<ReservationDetails> rd = new ArrayList<ReservationDetails>();
		
		try {
			connection = getConnection();
			if (connection != null) {
				String sql = "select * from ars1.journey j, ars1.reservation_details r," +
						" ars1.flight f where j.confirmation_number = r.confirmation_number " +
						"and j.flight_id = f.flight_id and j.confirmation_number = ?; ";
				
				stmt = connection.prepareStatement(sql);
				stmt.setLong(1, confirmationNumber);
				ResultSet rs = stmt.executeQuery();
				while(rs.next()){
					flight.setAirlineName(rs.getString("airline"));
					flight.setSource(rs.getString("source"));
					flight.setDestination(rs.getString("destination"));
					flight.setIntermediate(rs.getString("intermediate"));
					flight.setFlightNumber(rs.getString("flight_number"));
					flight.setTimeOfFlight(rs.getString("time_flight"));
					flight.setIntermediateArrivalTime(rs.getString("intermediate_arrival_time"));
					flight.setIntermediateDepartureTime(rs.getString("intermediate_departure_time"));	
					journey.setFlight(flight);
					journey.setBoardingPoint(rs.getString("boarding_point"));
					journey.setConfirmationNumber(rs.getLong("confirmation_number"));
					journey.setDateOfJourney(rs.getString("journey_date"));
					ReservationDetails r = new ReservationDetails();
					r.setAge(rs.getInt("age"));
					r.setFirstName(rs.getString("first_name"));
					r.setLastName(rs.getString("last_name"));
					rd.add(r);
				}
				ReservationDetails[] reservationDetails =  new ReservationDetails[rd.size()];
				journey.setReservationDetails(rd.toArray(reservationDetails));
					

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DAOException(e.getMessage());
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {
				throw new DAOException(ex.getMessage());
			}

		}
		return journey;
	}
	
	public List<Journey> viewAllJourney() throws DAOException
	{
		Statement stmt = null;
		Connection connection = null;
		List<Journey> allJourney = new ArrayList<Journey>();
		try
		{
			
			connection = getConnection();
			if (connection != null) {
				
				String SQL = "select * from ars1.journey,ars1.flight where journey.flight_id=flight.flight_id";
				stmt = connection.createStatement();
				ResultSet resultSet = stmt.executeQuery(SQL);
				while (resultSet.next()) {
					Journey journey=new Journey();
				     Flight flight=new Flight();
				    
				    flight.setAirlineName(resultSet.getString("airline"));
				    flight.setDestination(resultSet.getString("destination"));
				    flight.setFlightId(resultSet.getInt("flight_id"));
				    flight.setFlightNumber(resultSet.getString("flight_number"));
				    flight.setIntermediate(resultSet.getString("intermediate"));
				    flight.setIntermediateArrivalTime(resultSet.getString("intermediate_arrival_time"));
				    flight.setIntermediateDepartureTime(resultSet.getString("intermediate_departure_time"));
				    flight.setNumSeats(resultSet.getInt("numSeats"));
				    flight.setPilotName(resultSet.getString("pilot_name"));
				    flight.setSource(resultSet.getString("source"));
				    
				    flight.setTicketPrice(resultSet.getDouble("ticket_price"));
				    flight.setTimeOfFlight(resultSet.getString("time_flight"));
				    
				    journey.setFlight(flight);
				    journey.setBoardingPoint(resultSet.getString("boarding_point"));
					journey.setConfirmationNumber(resultSet.getLong("confirmation_number"));
					journey.setDateOfJourney(resultSet.getString("journey_date"));
					journey.setNumSeatsBooked(resultSet.getInt("seats_booked"));
					journey.setTicketIssueDate(resultSet.getString("ticket_issue_date"));
					
					allJourney.add(journey);
				}
				
			}
			
		}
			catch(Exception e){e.printStackTrace();}
		finally {
			try {
				stmt.close();
				connection.close();
			} 
			catch (Exception ex) {
}
		
	}
		return allJourney;
		
	}
		
	public List<ReservationDetails> viewAllReservation(long confirmationNumber) throws DAOException{
		Statement stmt = null;
		Connection connection = null;
		List<ReservationDetails> allReservations = new ArrayList<ReservationDetails>();
		try
		{
			
			connection = getConnection();
			if (connection != null) {
				String SQL = "select * from ars1.reservation_details where confirmation_number='"+confirmationNumber+"';";
				stmt = connection.createStatement();
				ResultSet resultSet = stmt.executeQuery(SQL);
				
				while(resultSet.next()){
					ReservationDetails reservation=new ReservationDetails();
					reservation.setAge(resultSet.getInt("age"));
					reservation.setConfirmationNumber(resultSet.getInt("confirmation_number"));
					reservation.setFirstName(resultSet.getString("first_name"));
					reservation.setLastName(resultSet.getString("last_name"));
					
					allReservations.add(reservation);
				}
				
			}
			
		}catch(Exception e){e.printStackTrace();}
		finally {
			try {
				stmt.close();
				connection.close();
			} 
			catch (Exception ex) {
}
		
	}
	
		return allReservations;
		
		
	}





	@Override
	public void deleteReservation(long confirmationNumber) throws DAOException {
		Statement stmt = null;
		Connection connection = null;
		
		try {
			connection = getConnection();
			if (connection != null) {
				String sql1 = "delete from ars1.reservation_details where confirmation_number = " + confirmationNumber;
				String sql2 = "delete from ars1.journey where confirmation_number = " + confirmationNumber;
				stmt = connection.createStatement();
				stmt.addBatch(sql1);
				stmt.addBatch(sql2);
				stmt.executeBatch();	
			
			}

		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {
				throw new DAOException(ex.getMessage());
			}

		}
		
		
	}
	
	
	public static void main(String args[]) throws DAOException{
		new JourneyDAOImpl().postFlightUpdate(1, "2010-05-01", "Flight delayed by 2 hr");
	}





	@Override
	public List<Journey> viewReservationsForTraveller(long personId)
			throws DAOException {
		PreparedStatement stmt = null;
		Connection connection = null;
		List<Journey> lstJourney = new ArrayList<Journey>();
		
		
		
		try {
			connection = getConnection();
			if (connection != null) {
				String sql = "select * from ars1.journey j, ars1.flight f where  j.flight_id = f.flight_id and person_id=? and journey_date>= now() order by confirmation_number desc;" ;
				
				stmt = connection.prepareStatement(sql);
				stmt.setLong(1, personId);
				ResultSet rs = stmt.executeQuery();
				while(rs.next()){
					Journey journey = new Journey();
					Flight flight = new Flight();
					flight.setAirlineName(rs.getString("airline"));
					flight.setSource(rs.getString("source"));
					flight.setDestination(rs.getString("destination"));
					flight.setIntermediate(rs.getString("intermediate"));
					flight.setFlightNumber(rs.getString("flight_number"));
					flight.setTimeOfFlight(rs.getString("time_flight"));
					flight.setIntermediateArrivalTime(rs.getString("intermediate_arrival_time"));
					flight.setIntermediateDepartureTime(rs.getString("intermediate_departure_time"));	
					journey.setFlight(flight);
					journey.setBoardingPoint(rs.getString("boarding_point"));
					journey.setConfirmationNumber(rs.getLong("confirmation_number"));
					journey.setDateOfJourney(rs.getString("journey_date"));
					lstJourney.add(journey);
				}
				

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DAOException(e.getMessage());
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {
				throw new DAOException(ex.getMessage());
			}

		}
		return lstJourney;
		
	}
	
	public List<ReservationDetails> viewAllPassengers(long flightId,String journeyDate)throws DAOException
	{
		PreparedStatement stmt = null;
		Connection connection = null;
		List<ReservationDetails> lstReservation = new ArrayList<ReservationDetails>();
	
		
		
		try {
			connection = getConnection();
			if (connection != null) {
				String sql = "select * from ars1.journey j," +
						" ars1.reservation_details r where  j.confirmation_number = r.confirmation_number and flight_id='"+flightId+"' and journey_date='"+journeyDate+"';" ;
				
				System.out.println(sql);
				
				stmt = connection.prepareStatement(sql);
				ResultSet rs = stmt.executeQuery();
				while(rs.next()){
				
					ReservationDetails reservationDetails = new ReservationDetails();
					reservationDetails.getAge();
					reservationDetails.setConfirmationNumber(rs.getLong("confirmation_number"));
					reservationDetails.setFirstName(rs.getString("first_name"));
					reservationDetails.setLastName(rs.getString("last_name"));
					reservationDetails.setAge(rs.getInt("age"));
				
					lstReservation.add(reservationDetails);
				}
				
					

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {
				throw new DAOException(ex.getMessage());
			}

		}
		return lstReservation;
	
	
	}
	
	
	public List<Journey> viewJourneyByDate()throws DAOException {
		PreparedStatement stmt = null;
		Connection connection = null;
		List<Journey> lstJourney = new ArrayList<Journey>();
		
		
		
		try {
			connection = getConnection();
			if (connection != null) {
				String sql = "select distinct f.*, journey_date, count(*) numBookedSeats" +
						" from ars1.flight f, ars1.journey j where f.flight_id = j.flight_id and journey_date > now() " +
						"group by f.flight_id, journey_date order by journey_date; ";
				
				stmt = connection.prepareStatement(sql);
		
				ResultSet rs = stmt.executeQuery();
				while(rs.next()){
					Journey journey = new Journey();
					Flight flight = new Flight();
					flight.setAirlineName(rs.getString("airline"));
					flight.setFlightId(rs.getLong("flight_id"));
					flight.setSource(rs.getString("source"));
					flight.setDestination(rs.getString("destination"));
					flight.setIntermediate(rs.getString("intermediate"));
					flight.setFlightNumber(rs.getString("flight_number"));
					flight.setTimeOfFlight(rs.getString("time_flight"));
					flight.setIntermediateArrivalTime(rs.getString("intermediate_arrival_time"));
					flight.setIntermediateDepartureTime(rs.getString("intermediate_departure_time"));	
					flight.setNumSeats(rs.getInt("numBookedSeats"));
					journey.setFlight(flight);
					journey.setDateOfJourney(rs.getString("journey_date"));
					lstJourney.add(journey);
				}
				

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DAOException(e.getMessage());
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {
				throw new DAOException(ex.getMessage());
			}

		}
		return lstJourney;
		
	}
	
	public void postFlightUpdate (long flightId, String journeyDate, String message) throws DAOException{
		CallableStatement stmt = null;
		Connection connection = null;
		System.out.println("Hi ............");
				
		try {
			connection = getConnection();
			if (connection != null) {
				stmt = connection.prepareCall("Call Postflightupdate (?,?,?)");
				stmt.setLong(1,flightId);
				stmt.setString(2, journeyDate);
				stmt.setString(3, message);
				System.out.println(stmt);
				stmt.executeUpdate();
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new DAOException(e.getMessage());
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {
				throw new DAOException(ex.getMessage());
			}

		}
		
	}
	
	
	

}
